SnowPro Advanced Administrator ADA-C02 v1.0
Question 1
A team is provisioning new lower environments from the production database using cloning. All production objects and references reside in the database, and do not have external references.
What set of object references needs to be re-pointed before granting access for usage?
- A. Sequences, views, and secure views
- B. Sequences, views, secure views, and materialized views
- C. Sequences, storage integrations, views, secure views, and materialized views
- D. There are no object references that need to be re-pointed
Answer : D
Question 2
An Administrator has a user who needs to be able to suspend and resume a task based on the current virtual warehouse load, but this user should not be able to modify the task or start a new run.
What privileges should be granted to the user to meet these requirements? (Choose two.)
- A. EXECUTE TASK on the task
- B. OWNERSHIP on the task
- C. OPERATE on the task
- D. USAGE on the database and schema containing the task
- E. OWNERSHIP on the database and schema containing the task
Answer : CD
Question 3
What is a characteristic of Snowflake's transaction locking and concurrency modeling?
- A. A deadlock cannot occur in Snowflake, since concurrently executed queries and DML statements do not block one another.
- B. If two queries are concurrently executed against the same table, one of the two queries will be blocked until the other query completes.
- C. Transaction locking in Snowflake is enforced exclusively at the row and table levels.
- D. Queries executed within a given transaction see that transaction's uncommitted changes.
Answer : A
Question 4
In which scenario will use of an external table simplify a data pipeline?
- A. When accessing a Snowflake table from a relational database
- B. When accessing a Snowflake table from an external database within the same region
- C. When continuously writing data from a Snowflake table to external storage
- D. When accessing a Snowflake table that references data files located in cloud storage
Answer : D
Question 5
What are the MINIMUM grants required on the database, schema, and table for a stream to be properly created and managed?
-
A.
Database: Usage
Schema: Usage
Table: Select, Create Stream -
B.
Database: Usage
Schema: Usage
Table: Select -
C.
Database: Usage, Create Stream
Schema: Usage
Table: Select -
D.
Database: Usage
Schema: Usage, Create Stream
Table: Select
Answer : D
Question 6
An Administrator loads data into a staging table every day. Once loaded, users from several different departments perform transformations on the data and load it into different production tables.
How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?
- A. Create it as an external table, which will not incur Time Travel costs.
- B. Create it as a transient table with a retention time of 0 days.
- C. Create it as a temporary table with a retention time of 0 days.
- D. Create it as a permanent table with a retention time of 0 days.
Answer : B
Question 7
An Administrator needs to configure a virtual warehouse in Snowflake for a new use case with these details:
1. There will be 50-100 concurrent users running reports
2. Minimize costs based on the number of active users at a given time
3. The virtual warehouse should suspend and resume automatically based on user activity
Which configuration will meet these requirements?
-
A.
-
B.
-
C.
-
D.
Answer : D
Question 8
When does auto-suspend occur for a multi-cluster virtual warehouse?
- A. When there has been no activity on any cluster for the specified period of time.
- B. After a specified period of time when an additional cluster has started on the maximum number of clusters specified for a warehouse.
- C. When the minimum number of clusters is running and there is no activity for the specified period of time.
- D. Auto-suspend does not apply for multi-cluster warehouses.
Answer : C
Question 9
An organization's sales team leverages this Snowflake query a few times a day:
What can the Snowflake Administrator do to optimize the use of persisted query results whenever possible?
- A. Wrap the query in a User-Defined Function (UDF) to match syntax execution.
- B. Assign everyone on the sales team to the same virtual warehouse.
- C. Assign everyone on the sales team to the same security role.
- D. Leverage the CURRENT_DATE function for date calculations.
Answer : D
Question 10
A retailer uses a TRANSACTIONS table (100M rows, 1.2 TB) that has been clustered by the STORE_ID column (varchar(50)). The vast majority of analyses on this table are grouped by STORE_ID to look at store performance.
There are 1000 stores operated by the retailer but most sales come from only 20 stores. The Administrator notes that most queries are currently experiencing poor pruning, with large amounts of bytes processed by even simple queries.
Why is this occurring?
- A. The STORE_ID should be numeric.
- B. The table is not big enough to take advantage of the clustering key.
- C. Sales across stores are not uniformly distributed.
- D. The cardinality of the stores to transaction count ratio is too low to use the STORE_ID as a clustering key.
Answer : C
Question 11
A Snowflake Administrator has a multi-cluster virtual warehouse and is using the Snowflake Business-Critical edition. The minimum number of clusters is set to 2 and the maximum number of clusters is set to 10. This configuration works well for the standard workload, rarely exceeding 5 running clusters. However, once a month the Administrator notes that there are a few complex long-running queries that are causing increased queue time and the warehouse reaches its maximum limit at 10 clusters.
Which solutions will address the issues happening once a month? (Choose two.)
- A. Use a task to increase the cluster size for the time period that the more complex queries are running and another task to reduce the size of the cluster once the complex queries complete.
- B. Have the group running the complex monthly queries use a separate appropriately-sized warehouse to support their workload.
- C. Increase the multi-cluster maximum to 20 or more clusters.
- D. Examine the complex queries and determine if they can be made more efficient using clustering keys or materialized views.
- E. Increase the minimum number of clusters started in the multi-cluster configuration to 5.
Answer : BD
Question 12
A Snowflake Administrator wants to create a virtual warehouse that supports several dashboards, issuing various queries on the same database.
For this warehouse, why should the Administrator consider setting AUTO_SUSPEND to 0 or NULL?
- A. To save costs on warehouse shutdowns and startups for different queries
- B. To save costs by running the warehouse as little as possible
- C. To keep the data cache warm to support good performance of similar queries
- D. To keep the query result cache warm for good performance on repeated queries
Answer : C
Question 13
If the query matches the definition, will Snowflake always dynamically rewrite the query to use a materialized view?
- A. No, because joins are not supported by materialized views.
- B. No, because the optimizer might decide against it.
- C. No, because the materialized view may not be up-to-date.
- D. Yes, because materialized views are always faster.
Answer : B
Question 14
A Snowflake customer is experiencing higher costs than anticipated while migrating their data warehouse workloads from on-premises to Snowflake. The migration workloads have been deployed on a single warehouse and are characterized by a large number of small INSERTS rather than bulk loading of large extracts. That single warehouse has been configured as a single cluster, 2XL because there are many parallel INSERTs that are scheduled during nightly loads.
How can the Administrator reduce the costs, while minimizing the overall load times, for migrating data warehouse history?
- A. There should be another 2XL warehouse deployed to handle a portion of the load queries.
- B. The 2XL warehouse should be changed to 4XL to increase the number of threads available for parallel load queries.
- C. The warehouse should be kept as a SMALL or XSMALL and configured as a multi-cluster warehouse to handle the parallel load queries.
- D. The INSERTs should be converted to several tables to avoid contention on large tables that slows down query processing.
Answer : C
Question 15
A company has many users in the role ANALYST who routinely query Snowflake through a reporting tool. The Administrator has noticed that the ANALYST users keep two small clusters busy all of the time, and occasionally they need three or four clusters of that size.
Based on this scenario, how should the Administrator set up a virtual warehouse to MOST efficiently support this group of users?
- A. Create a multi-cluster warehouse with MIN_CLUSTERS set to 1. Give MANAGE privileges to the ANALYST role so this group can start and stop the warehouse, and increase the number of clusters as needed.
- B. Create a multi-cluster warehouse with MIN_CLUSTERS set to 2. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role. Allow the warehouse to auto-scale.
- C. Create a standard X-Large warehouse, which is equivalent to four small clusters. Set the warehouse to auto-resume and auto-suspend, and give USAGE privileges to the ANALYST role.
- D. Create four virtual warehouses (sized Small through XL) and set them to auto-suspend and auto-resume. Have users in the ANALYST role select the appropriate warehouse based on how many queries are being run.
Answer : B